import sys
import os
import logging
import json
from typing import Dict, List, Optional, Any
from decimal import Decimal

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_各学历及总体_毕业去向分布_人数及占比(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None,
    education: Optional[str] = None
) -> Dict[str, Any]:
    """
    各学历及总体，毕业去向分布，人数及占比 - 指标计算函数
    
    ## 指标说明
    该指标用于统计特定项目中不同学历（本科、专科、硕士、博士）及总体的毕业生去向分布情况，包括各去向的人数及其占总人数的比例。
    提供最明细的数据：按学历+院系+专业+一级分类+二级分类的分布，同时提供按学历汇总和总体汇总数据。
    数据包含两级分类：
    1. 一级分类（grad_dest_merge）：升学、单位就业等大类
    2. 二级分类（grad_dest）：境内升学、境外留学、签就业协议形式就业等详细类型
    数据来源于学生客观表，按照毕业去向字段分组统计，并计算各去向的人数和占比。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        education (Optional[str]): 学历筛选条件，可选值：[本科毕业生, 专科毕业生, 硕士研究生, 博士研究生]
        
    ## 示例
    ### 输入示例1：不指定学历（返回所有学历明细）
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出示例1：包含最明细数据、学历汇总、总体汇总（都包含一级和二级分类）
    ```json
    {
        "success": true,
        "message": "ok",
        "code": 0,
        "result": [
            {
                "type": "明细",
                "education": "本科毕业生",
                "college": "计算机学院",
                "spec": "计算机科学与技术",
                "total": 120,
                "data": [
                    {
                        "一级分类": "单位就业",
                        "一级人数": 80,
                        "一级占比": 0.6667,
                        "明细": [
                            {
                                "二级分类": "签就业协议形式就业",
                                "人数": 50,
                                "占比": 0.4167
                            },
                            {
                                "二级分类": "签劳动合同形式就业",
                                "人数": 30,
                                "占比": 0.25
                            }
                        ]
                    },
                    {
                        "一级分类": "升学",
                        "一级人数": 30,
                        "一级占比": 0.25,
                        "明细": [
                            {
                                "二级分类": "境内升学",
                                "人数": 25,
                                "占比": 0.2083
                            },
                            {
                                "二级分类": "境外留学",
                                "人数": 5,
                                "占比": 0.0417
                            }
                        ]
                    },
                    {
                        "一级分类": "待就业",
                        "一级人数": 10,
                        "一级占比": 0.0833,
                        "明细": [
                            {
                                "二级分类": "待就业",
                                "人数": 10,
                                "占比": 0.0833
                            }
                        ]
                    }
                ]
            },
            {
                "type": "学历汇总",
                "group": "本科毕业生",
                "total": 1000,
                "data": [
                    {
                        "一级分类": "单位就业",
                        "一级人数": 700,
                        "一级占比": 0.7,
                        "明细": [
                            {
                                "二级分类": "签就业协议形式就业",
                                "人数": 450,
                                "占比": 0.45
                            },
                            {
                                "二级分类": "签劳动合同形式就业",
                                "人数": 250,
                                "占比": 0.25
                            }
                        ]
                    },
                    {
                        "一级分类": "升学",
                        "一级人数": 200,
                        "一级占比": 0.2,
                        "明细": [
                            {
                                "二级分类": "境内升学",
                                "人数": 150,
                                "占比": 0.15
                            },
                            {
                                "二级分类": "境外留学",
                                "人数": 50,
                                "占比": 0.05
                            }
                        ]
                    },
                    {
                        "一级分类": "待就业",
                        "一级人数": 100,
                        "一级占比": 0.1,
                        "明细": [
                            {
                                "二级分类": "待就业",
                                "人数": 100,
                                "占比": 0.1
                            }
                        ]
                    }
                ]
            },
            {
                "type": "总体汇总",
                "group": "总体",
                "total": 2000,
                "data": [
                    {
                        "一级分类": "单位就业",
                        "一级人数": 1300,
                        "一级占比": 0.65,
                        "明细": [
                            {
                                "二级分类": "签就业协议形式就业",
                                "人数": 800,
                                "占比": 0.4
                            },
                            {
                                "二级分类": "签劳动合同形式就业",
                                "人数": 500,
                                "占比": 0.25
                            }
                        ]
                    },
                    {
                        "一级分类": "升学",
                        "一级人数": 500,
                        "一级占比": 0.25,
                        "明细": [
                            {
                                "二级分类": "境内升学",
                                "人数": 380,
                                "占比": 0.19
                            },
                            {
                                "二级分类": "境外留学",
                                "人数": 120,
                                "占比": 0.06
                            }
                        ]
                    },
                    {
                        "一级分类": "待就业",
                        "一级人数": 200,
                        "一级占比": 0.1,
                        "明细": [
                            {
                                "二级分类": "待就业",
                                "人数": 200,
                                "占比": 0.1
                            }
                        ]
                    }
                ]
            }
        ]
    }
    ```
    
    ### 输入示例2：指定学历
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159],
        "education": "博士研究生"
    }
    ```
    
    ### 输出示例2：指定学历时返回该学历的明细和汇总（都包含一级和二级分类）
    ```json
    {
        "success": true,
        "message": "ok",
        "code": 0,
        "result": [
            {
                "type": "明细",
                "education": "博士研究生",
                "college": "理学院",
                "spec": "数学",
                "total": 50,
                "data": [
                    {
                        "一级分类": "单位就业",
                        "一级人数": 30,
                        "一级占比": 0.6,
                        "明细": [
                            {
                                "二级分类": "签就业协议形式就业",
                                "人数": 20,
                                "占比": 0.4
                            },
                            {
                                "二级分类": "签劳动合同形式就业",
                                "人数": 10,
                                "占比": 0.2
                            }
                        ]
                    },
                    {
                        "一级分类": "升学",
                        "一级人数": 20,
                        "一级占比": 0.4,
                        "明细": [
                            {
                                "二级分类": "境内升学",
                                "人数": 15,
                                "占比": 0.3
                            },
                            {
                                "二级分类": "境外留学",
                                "人数": 5,
                                "占比": 0.1
                            }
                        ]
                    }
                ]
            },
            {
                "type": "学历汇总",
                "group": "博士研究生",
                "total": 300,
                "data": [
                    {
                        "一级分类": "单位就业",
                        "一级人数": 150,
                        "一级占比": 0.5,
                        "明细": [
                            {
                                "二级分类": "签就业协议形式就业",
                                "人数": 100,
                                "占比": 0.3333
                            },
                            {
                                "二级分类": "签劳动合同形式就业",
                                "人数": 50,
                                "占比": 0.1667
                            }
                        ]
                    },
                    {
                        "一级分类": "升学",
                        "一级人数": 120,
                        "一级占比": 0.4,
                        "明细": [
                            {
                                "二级分类": "境内升学",
                                "人数": 90,
                                "占比": 0.3
                            },
                            {
                                "二级分类": "境外留学",
                                "人数": 30,
                                "占比": 0.1
                            }
                        ]
                    },
                    {
                        "一级分类": "待就业",
                        "一级人数": 30,
                        "一级占比": 0.1,
                        "明细": [
                            {
                                "二级分类": "待就业",
                                "人数": 30,
                                "占比": 0.1
                            }
                        ]
                    }
                ]
            },
            {
                "type": "总体汇总",
                "group": "总体",
                "total": 2000,
                "data": [
                    {
                        "一级分类": "单位就业",
                        "一级人数": 1300,
                        "一级占比": 0.65,
                        "明细": [
                            {
                                "二级分类": "签就业协议形式就业",
                                "人数": 800,
                                "占比": 0.4
                            },
                            {
                                "二级分类": "签劳动合同形式就业",
                                "人数": 500,
                                "占比": 0.25
                            }
                        ]
                    },
                    {
                        "一级分类": "升学",
                        "一级人数": 500,
                        "一级占比": 0.25,
                        "明细": [
                            {
                                "二级分类": "境内升学",
                                "人数": 380,
                                "占比": 0.19
                            },
                            {
                                "二级分类": "境外留学",
                                "人数": 120,
                                "占比": 0.06
                            }
                        ]
                    },
                    {
                        "一级分类": "待就业",
                        "一级人数": 200,
                        "一级占比": 0.1,
                        "明细": [
                            {
                                "二级分类": "待就业",
                                "人数": 200,
                                "占比": 0.1
                            }
                        ]
                    }
                ]
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 各学历及总体，毕业去向分布，人数及占比, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year FROM client_item WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = client_code.lstrip('ABCDEFGHIJKLMNOPQRSTUVWXYZ').lstrip('0')
        if not shard_tb_key:
            raise ValueError(f"无效的client_code: {client_code}")
        
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 查询学生数据
        student_table = f"dim_client_target_baseinfo_student_calc_{item_year}"
        
        # 4. 处理学历参数（空字符串转为None）
        if education == '':
            education = None
        
        # 5. 构建学历筛选条件
        education_filter = ""
        if education:
            if education not in ["本科毕业生", "专科毕业生", "硕士研究生", "博士研究生"]:
                raise ValueError(f"无效的学历参数: {education}")
            education_filter = f"AND education = '{education}'"
        
        logger.info(f"学历筛选: {education if education else '不限制，返回所有学历明细'}")
        
        # 6. 查询最明细数据：学历+院系+专业+毕业去向（包含一级和二级分类）
        logger.info("开始查询最明细数据：学历+院系+专业+毕业去向（包含一级和二级分类）")
        detail_sql = f"""
        SELECT
            education,
            college,
            spec,
            grad_dest_merge,
            grad_dest,
            COUNT(*) as count
        FROM
            {student_table}
        WHERE
            shard_tb_key = %s
            {education_filter}
        GROUP BY education, college, spec, grad_dest_merge, grad_dest
        ORDER BY education, college, spec, grad_dest_merge, grad_dest
        """
        detail_results = db.fetchall(detail_sql, (shard_tb_key,))
        
        if not detail_results:
            raise ValueError("未找到毕业去向分布数据")
        
        # 7. 处理明细数据，构建多层级结构（包含一级和二级分类）
        # 7.1 按学历+院系+专业+一级分类+二级分类分组
        detail_dict = {}
        education_dict = {}  # 按学历汇总（一级分类）
        overall_total = 0
        
        for row in detail_results:
            edu = row['education'] or '总体'
            college = row['college'] or ''
            spec = row['spec'] or ''
            grad_dest_merge = row['grad_dest_merge'] or ''  # 一级分类
            grad_dest = row['grad_dest'] or ''  # 二级分类
            count = row['count']
            
            overall_total += count
            
            # 构建明细数据（按一级分类分组，包含二级分类明细）
            if edu not in detail_dict:
                detail_dict[edu] = {}
            if college not in detail_dict[edu]:
                detail_dict[edu][college] = {}
            if spec not in detail_dict[edu][college]:
                detail_dict[edu][college][spec] = {
                    'total': 0,
                    'categories': {}  # 按一级分类分组
                }
            
            detail_dict[edu][college][spec]['total'] += count
            
            # 按一级分类分组
            if grad_dest_merge not in detail_dict[edu][college][spec]['categories']:
                detail_dict[edu][college][spec]['categories'][grad_dest_merge] = {
                    '一级分类': grad_dest_merge,
                    '一级人数': 0,
                    '一级占比': 0,
                    '明细': []
                }
            
            detail_dict[edu][college][spec]['categories'][grad_dest_merge]['一级人数'] += count
            detail_dict[edu][college][spec]['categories'][grad_dest_merge]['明细'].append({
                '二级分类': grad_dest,
                '人数': count
            })
            
            # 按学历汇总（按一级分类分组）
            if edu not in education_dict:
                education_dict[edu] = {}
            if grad_dest_merge not in education_dict[edu]:
                education_dict[edu][grad_dest_merge] = {
                    '一级分类': grad_dest_merge,
                    '一级人数': 0,
                    '一级占比': 0,
                    '明细': {}
                }
            
            education_dict[edu][grad_dest_merge]['一级人数'] += count
            if grad_dest not in education_dict[edu][grad_dest_merge]['明细']:
                education_dict[edu][grad_dest_merge]['明细'][grad_dest] = 0
            education_dict[edu][grad_dest_merge]['明细'][grad_dest] += count
        
        # 7.2 构建返回结果
        result_data = []
        
        # 7.2.1 添加最明细数据（学历+院系+专业+一级分类+二级分类）
        for edu, colleges in detail_dict.items():
            for college, specs in colleges.items():
                for spec, spec_data in specs.items():
                    total = spec_data['total']
                    categories = []
                    
                    # 处理每个一级分类
                    for grad_dest_merge, category_info in spec_data['categories'].items():
                        category_count = category_info['一级人数']
                        category_ratio = round(float(category_count) / float(total), 4) if total > 0 else 0
                        
                        # 处理二级分类明细
                        details = []
                        for detail in category_info['明细']:
                            detail_count = detail['人数']
                            detail_ratio = round(float(detail_count) / float(total), 4) if total > 0 else 0
                            details.append({
                                '二级分类': detail['二级分类'],
                                '人数': detail_count,
                                '占比': detail_ratio
                            })
                        # 按人数降序排序
                        details.sort(key=lambda x: x['人数'], reverse=True)
                        
                        categories.append({
                            '一级分类': grad_dest_merge,
                            '一级人数': category_count,
                            '一级占比': category_ratio,
                            '明细': details
                        })
                    
                    # 按一级人数降序排序
                    categories.sort(key=lambda x: x['一级人数'], reverse=True)
                    
                    result_data.append({
                        'type': '明细',
                        'education': edu,
                        'college': college,
                        'spec': spec,
                        'total': total,
                        'data': categories
                    })
        
        # 7.2.2 添加按学历汇总数据（包含一级和二级分类）
        for edu, categories_dict in education_dict.items():
            total = sum(cat['一级人数'] for cat in categories_dict.values())
            categories = []
            
            for grad_dest_merge, category_info in categories_dict.items():
                category_count = category_info['一级人数']
                category_ratio = round(float(category_count) / float(total), 4) if total > 0 else 0
                
                # 处理二级分类明细
                details = []
                for grad_dest, detail_count in category_info['明细'].items():
                    detail_ratio = round(float(detail_count) / float(total), 4) if total > 0 else 0
                    details.append({
                        '二级分类': grad_dest,
                        '人数': detail_count,
                        '占比': detail_ratio
                    })
                # 按人数降序排序
                details.sort(key=lambda x: x['人数'], reverse=True)
                
                categories.append({
                    '一级分类': grad_dest_merge,
                    '一级人数': category_count,
                    '一级占比': category_ratio,
                    '明细': details
                })
            
            # 按一级人数降序排序
            categories.sort(key=lambda x: x['一级人数'], reverse=True)
            
            result_data.append({
                'type': '学历汇总',
                'group': edu,
                'total': total,
                'data': categories
            })
        
        # 7.2.3 添加总体汇总数据（包含一级和二级分类）
        overall_categories_dict = {}
        for edu, categories_dict in education_dict.items():
            for grad_dest_merge, category_info in categories_dict.items():
                if grad_dest_merge not in overall_categories_dict:
                    overall_categories_dict[grad_dest_merge] = {
                        '一级分类': grad_dest_merge,
                        '一级人数': 0,
                        '一级占比': 0,
                        '明细': {}
                    }
                
                overall_categories_dict[grad_dest_merge]['一级人数'] += category_info['一级人数']
                for grad_dest, detail_count in category_info['明细'].items():
                    if grad_dest not in overall_categories_dict[grad_dest_merge]['明细']:
                        overall_categories_dict[grad_dest_merge]['明细'][grad_dest] = 0
                    overall_categories_dict[grad_dest_merge]['明细'][grad_dest] += detail_count
        
        overall_categories = []
        for grad_dest_merge, category_info in overall_categories_dict.items():
            category_count = category_info['一级人数']
            category_ratio = round(float(category_count) / float(overall_total), 4) if overall_total > 0 else 0
            
            # 处理二级分类明细
            details = []
            for grad_dest, detail_count in category_info['明细'].items():
                detail_ratio = round(float(detail_count) / float(overall_total), 4) if overall_total > 0 else 0
                details.append({
                    '二级分类': grad_dest,
                    '人数': detail_count,
                    '占比': detail_ratio
                })
            # 按人数降序排序
            details.sort(key=lambda x: x['人数'], reverse=True)
            
            overall_categories.append({
                '一级分类': grad_dest_merge,
                '一级人数': category_count,
                '一级占比': category_ratio,
                '明细': details
            })
        
        # 按一级人数降序排序
        overall_categories.sort(key=lambda x: x['一级人数'], reverse=True)
        
        result_data.append({
            'type': '总体汇总',
            'group': '总体',
            'total': overall_total,
            'data': overall_categories
        })
        
        if not result_data:
            raise ValueError("未找到任何毕业去向数据")
        
        logger.info(f"指标 '各学历及总体，毕业去向分布，人数及占比' 计算成功，返回 {len(result_data)} 条记录（包含明细、学历汇总、总体汇总）")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": result_data
        }

    except Exception as e:
        logger.error(f"计算指标 '各学历及总体，毕业去向分布，人数及占比' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 各学历及总体，毕业去向分布，人数及占比",
            "code": 500,
            "error": str(e)
        }